﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetMyPostil]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetMyPostil];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetMyPostil]
    @StaffName nvarchar(300)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @staffid int;

SELECT @staffid = staff_id FROM dbo.uds_staff WHERE staff_name = @StaffName;
IF @@rowcount <> 1
BEGIN
	raiserror(N'The specified user "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
END;

--待批文档
SELECT
		a.Doc_ID,
		a.a Title,
		b.Doc_Builder_ID,
		CONVERT(nvarchar(10), Doc_Added_Date, 120) AS Short_Doc_Added_Date,
		b.Doc_Status,
		b.Flow_ID,
		b.Step_ID,
		c.realname AS DocBuilder,
		d.flow_name AS FlowName,
		(
			CASE b.doc_status 
				WHEN 0 THEN N'未签'
				WHEN 1 THEN N'待批'
				WHEN 2 THEN N'会签中'
				WHEN 3 THEN N'成功'
				WHEN 4 THEN N'拒绝'
			END
		) as status,
		f.step_name AS StepName
    FROM     
        dbo.uds_flow_Style_Data a,
        dbo.uds_flow_document b,
        dbo.uds_staff c,
        dbo.uds_flow d,
        dbo.uds_flow_status e,
        dbo.uds_flow_step f
    WHERE
		a.doc_ID = b.doc_id 
        and b.flow_id = f.flow_id
        and b.step_id = f.step_id
        and b.flow_id = d.flow_id
        and b.doc_builder_id = c.staff_id
        and b.doc_id = e.doc_id
        and e.staff_id = @staffid
        and (
				(e.status =0 and (b.doc_status =2 or b.doc_status=0))
				or e.status = 1
			)
        and b.isrunning = 1
    ORDER BY Doc_Added_Date DESC;

END
GO